#!/usr/bin/env python
# -*- coding: utf-8 -*-

from openpyxl import Workbook, load_workbook
from datetime import datetime
from typing import List, Dict, Any
from fastapi import HTTPException, status

def validate_employee_data(data: Dict[str, Any]) -> Dict[str, Any]:
    """验证员工数据"""
    try:
        # 必填字段验证
        required_fields = ['name', 'gender', 'birth_date', 'id_card', 'phone', 'department_id', 'position']
        for field in required_fields:
            if not data.get(field):
                raise ValueError(f"缺少必填字段: {field}")
        
        # 数据类型转换和验证
        if not isinstance(data['gender'], int) or data['gender'] not in [1, 2]:
            raise ValueError("性别必须是1(男)或2(女)")
        
        # 转换日期格式
        if isinstance(data['birth_date'], str):
            data['birth_date'] = datetime.strptime(data['birth_date'], '%Y-%m-%d').date()
        
        # 验证手机号格式
        if not str(data['phone']).isdigit() or len(str(data['phone'])) != 11:
            raise ValueError("手机号格式不正确")
        
        return data
    except ValueError as e:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=str(e)
        )

def read_employee_excel(file_path: str) -> List[Dict[str, Any]]:
    """读取员工Excel文件"""
    try:
        # 读取Excel文件
        wb = load_workbook(file_path)
        ws = wb.active
        
        # 获取表头
        headers = [cell.value for cell in ws[1]]
        
        # 验证必要的列是否存在
        required_columns = ['姓名', '性别', '出生日期', '身份证号', '手机号', '部门ID', '职位']
        missing_columns = [col for col in required_columns if col not in headers]
        if missing_columns:
            raise ValueError(f"Excel文件缺少必要的列: {', '.join(missing_columns)}")
        
        # 转换为字典列表
        employees = []
        for row in ws.iter_rows(min_row=2):
            row_data = {headers[i]: cell.value for i, cell in enumerate(row)}
            
            employee = {
                'name': row_data['姓名'],
                'gender': 1 if row_data['性别'] == '男' else 2,
                'birth_date': row_data['出生日期'],
                'id_card': str(row_data['身份证号']),
                'phone': str(row_data['手机号']),
                'department_id': int(row_data['部门ID']),
                'position': row_data['职位'],
                'address': row_data.get('地址'),
                'emergency_contact': row_data.get('紧急联系人'),
                'emergency_phone': row_data.get('紧急联系电话'),
                'remark': row_data.get('备注')
            }
            
            # 验证数据
            employee = validate_employee_data(employee)
            employees.append(employee)
        
        return employees
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"处理Excel文件失败: {str(e)}"
        )

def create_employee_excel(employees: List[Dict[str, Any]], file_path: str):
    """创建员工Excel文件"""
    try:
        wb = Workbook()
        ws = wb.active
        
        # 写入表头
        headers = ['姓名', '性别', '出生日期', '身份证号', '手机号', '部门ID', '职位', 
                  '地址', '紧急联系人', '紧急联系电话', '备注']
        ws.append(headers)
        
        # 写入数据
        for emp in employees:
            row = [
                emp['name'],
                '男' if emp['gender'] == 1 else '女',
                emp['birth_date'],
                emp['id_card'],
                emp['phone'],
                emp['department_id'],
                emp['position'],
                emp.get('address', ''),
                emp.get('emergency_contact', ''),
                emp.get('emergency_phone', ''),
                emp.get('remark', '')
            ]
            ws.append(row)
        
        # 保存文件
        wb.save(file_path)
        return file_path
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"创建Excel文件失败: {str(e)}"
        ) 